package vsqlbuilder.logic.sql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import vsqlbuilder.logic.Column;
import vsqlbuilder.logic.Table;

//TODO [low]: Do a SQL preprocessor that evaluate Generate SQL expressions and found errors, and if user modified generated SQL found that errors too.

public class Query {

	// TODO [low]: Optimize this class because probably use of not useful
	// operations, & redundant source code or classes not used

	private List<QueryTable> tables;
	private List<QueryJoin> joins;
	private QueryOrderedColumns columns;
	private QueryRestrictionsList restrictions;

	public Query() {
		this.tables = new ArrayList<QueryTable>();
		this.joins = new ArrayList<QueryJoin>();
		columns = new QueryOrderedColumns();
		restrictions = new QueryRestrictionsList();
	}

	public void addTableToQuery(Table table) {
		tables.add(new QueryTable(table));
	}

	public void addJoinToQuery(QueryJoin qj){
		joins.add(qj);
	}
	
	public void addColumnToQuery(Column column) {
		QueryColumn qc = new QueryColumn(column);
		// First Add column to the ordered list (for order inside columns
		// selected)
		columns.addColumn(qc);
		// Later Add Column to the QueryTable (for advanced query operations)
		for (QueryTable qt : this.tables) {
			if (qt.getOwnerSchema().equals(column.getParentTable().getSchemaName())) {
				if (qt.getTableName().equals(column.getParentTable().getName())) {
					qt.addColumnToQuery(qc);
				}
			}
		}
	}

	public void removeColumnFromQuery(Column column) {
		for (QueryTable qt : this.tables) {
			if (qt.getOwnerSchema().equals(column.getParentTable().getSchemaName())) {
				if (qt.getTableName().equals(column.getParentTable().getName())) {
					// first remove from query table object
					qt.removeColumnFromQuery(column.getName());
					// after remove from columns ordered list object
					columns
							.removeColumn(column.getParentTable()
									.getSchemaName(), column.getParentTable()
									.getName(), column.getName());
					break; // [BUG FIX#5] 4/4 if continue iterating over the
							// modified list a exception is throw when selecting
							// or unselecting columns for a query.
				}
			}
		}
	}

	public void removeTableFromQuery(Table table) {
		for (QueryTable qt : this.tables) {
			if (qt.getOwnerSchema().equals(table.getSchemaName())) {
				if (qt.getTableName().equals(table.getName())) {
					// First Remove all columns from the ordered list of columns
					for (int i = (columns.getNumberCols()); i > 0; i--) {
						columns
								.removeColumn(qt.getOwnerSchema(), qt
										.getTableName(), columns
										.getColumnNameAt(i - 1));
					}
					// After Remove all columns from this query table object
					// (but before unselect all his columns)
					qt.removeAllColumnsFromQuery();
					// Finally Remove the query table object
					tables.remove(qt);
					break; // [BUG FIX#5] 3/3 if continue iterating over the
							// modified list a exception is throw when selecting
							// or unselecting columns for a query.
				}
			}
		}
	}

	
	public void removeJoinFromQuery(QueryJoin qj){
		joins.remove(qj);
	}
	
	public List<QueryJoin> getJoinsList(){
		return this.joins;
	}

	public QueryRestriction AddRestrictionToQuery(int index){
		 //Solution to bug, after remove last restriction in panel and add a new one, is raised:java.lang.IndexOutOfBoundsException: Index: n, Size: n-1
		if(index==(this.restrictions.getList().size()+1)){
			return this.restrictions.addRestriction();
		}
		return this.restrictions.addRestriction(index);
	}
	
	public void removeRestrictionFromQuery(QueryRestriction qr){
		this.restrictions.removeRestriction(qr);
	}

	public List<QueryRestriction> getRestrictionsList(){
		return this.restrictions.getList();
	}
	
	public List<QueryTable> getTablesList(){
		return this.tables;
	}
	
	// TODO [medium]: Optimize alias generation for tables and use a standard
	// method
	public String GenerateSentence() {
		String alias;

		// Define a HashMap to store alias create for every table
		HashMap<String, String> tableAlias = new HashMap<String, String>();

		// Temporarily we are going to use letters as table alias (problem if
		// tables bigger than 27)
		char c = 'a';
		for (QueryTable qt : this.tables) {
			alias = new String(Character.toString(c));
			tableAlias.put(new String(qt.getOwnerSchema()+"."+qt.getTableName()), alias);
			c++;
		}
		int lastgroup=0;
		String output;
		output = "SELECT ";
		int numCols = columns.getNumberCols();
		for (QueryColumn qc : this.columns.getList()) {
			String value = (String) tableAlias.get(qc.getSchemaName()+"."+qc.getTableName());
			output += value + "." + qc.getName();
			if ((numCols--) > 1)
				output += ", ";
			else
				output += " ";
		}

		output += " FROM ";
		int numTabs = tables.size();
		for (QueryTable qt : this.tables) {
			String value = (String) tableAlias.get(qt.getOwnerSchema()+"."+qt.getTableName());
			output += qt.getOwnerSchema() + "." + qt.getTableName() + " "
					+ value;
			if ((numTabs--) > 1)
				output += ", ";
			else
				output += " ";
		}

		if(joins.size()>0 || getRestrictionsList().size()>0){
			output +=" WHERE ";			
		}

		int numJoins = joins.size();
		for (QueryJoin qj : this.joins) {
			String valueS= (String) tableAlias.get(qj.getSourceSchemaName()+"."+qj.getSourceTableName());
			output += valueS + "." + qj.getSourceColumnName();
			
			output+= qj.getJoinSymbol();
			
			String valueD= (String) tableAlias.get(qj.getDestinationSchemaName()+"."+qj.getDestinationTableName());
			output += valueD + "." + qj.getDestinationColumnName();
			
			
			//TODO [HIGH]: Right now Joins Connectors are just AND [This should modified to allow others ?????? until now = NO]
			if ((numJoins--) > 1)
				output += " AND ";
			else
				output += " ";
		}
		
		Iterator<QueryRestriction> it = getRestrictionsList().iterator();
		String conector="";
		//in case of exists query restriction then add an AND connector to allow the addition of this restrictions to query
		if(it.hasNext()){
			output+=" AND "; 
		}
		
		lastgroup=0;			

		
		while(it.hasNext()){
			QueryRestriction qr = (QueryRestriction) it.next();
			//if this is NOT a composite restriction
			if(qr.getGroup()==0){
				//if a composite restriction was processed before then close the parenthesis
				if(lastgroup!=0 ){
					output=output.substring(0,output.length()-4);//fix bug when you place restriction 1 - 2 - 3.1 3.2 - 4 "restriction3.2   AND  ) restriction4"
					output+=" ) "+conector+" ";
				}
				lastgroup=0;

				//Divide schema.table.column by the dot symbol in three strings to use it
				String[] parts = null; 
				parts = qr.getExpression().split("\\.");
				String aliasR = (String) tableAlias.get(parts[0]+"."+parts[1]);
				
				output += aliasR + "." + parts[2] + " "  + qr.getStringTypeRestriction()+" "+qr.getValue1()+" ";
				if(qr.getValue2()!=null){
					output+=" AND "+qr.getValue2() + " ";
				}
			//if this IS a composite restriction
			}else{
				//if this is the first restriction in a composite set of restrictions then open parenthesis
				if(lastgroup==0 && (qr.getGroup()!=0)){
					output+="( ";
				}
				
				//Divide schema.table.column by the dot symbol in three strings to use it
				String[] parts = null; 
				parts = qr.getExpression().split("\\.");
				String aliasR = (String) tableAlias.get(parts[0]+"."+parts[1]);
					
				output += aliasR + "." + parts[2] + " "  + qr.getStringTypeRestriction()+" "+qr.getValue1()+" ";
				if(qr.getValue2()!=null){
					output+=" AND "+qr.getValue2() + " ";
				}
						
				//if this is the last restriction in the composite restriction set, then close parenthesis and continue another
				//TODO [medium]: this should be fixed because it's a temporary fix for bug of two different composite restrictions one next to another
				if(lastgroup!=0 && lastgroup!=qr.getGroup()){
					output+=" ) ";						 
				}
				
				
				lastgroup=qr.getGroup();
				
			}
			if(it.hasNext()){
				conector=qr.getStringTypeConnector();
				output += " "+conector+" ";				
			}
		}
			
		if(lastgroup!=0){
			output += ");";
		}else{
			output += ";";			
		}


		return output;
	}

	// TODO [low]: This is only a very basic sketch of what must be done by this
	// function.
	// This function explain if this object is ready or not to produce some
	// useful query
	public boolean isReady() {
		boolean value;
		if ((this.tables.size() > 0) && (this.columns.getNumberCols() > 0)) {
			value = true;
		} else {
			value = false;
		}
		return value;
	}

	
}
